package sanrenxing.subjects.dao.impl;

import sanrenxing.course.domain.CourseName;
import sanrenxing.subjects.dao.SubjectsDao;
import sanrenxing.subjects.domain.Subjects;
import sanrenxing.subjects.domain.SubjectsTo;
import sanrenxing.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * 功能：
 * 编写者：阳赛
 * 日期：
 * 版本：1.0
 */
public class SubjectsDaoImpl implements SubjectsDao {
    /**
     * 将实体对象添加到数据库subjects表中
     * @param subjects 题库对象
     * @return 成功添加的行数(1 : 添加成功 ; 0 : 添加失败)
     */
    @Override
    public int add(Subjects subjects) {
        //声明数据库连接对象
        Connection conn=null;
        //声明预编译语句对象
        PreparedStatement pstmt=null;
        //定义影响行数的变量
        int rows=0;
        try {
            //实例化数据库连接对象
            conn= JdbcUtils.getConnection();
            //编写SQL语句字符串
            String sql="insert into subjects(subjectType,courseId,stem,optionA,optionB,optionC,optionD,optionE,optionF,optionG,optionH,correct,hard) values(?,?,?,?,?,?,?,?,?,?,?,?,?)";
        //实例化预编译语句对象
        pstmt=conn.prepareStatement(sql);
        //设置SQL语句中占位符赋值
        pstmt.setString(1,subjects.getSubjectType());
        pstmt.setInt(2,subjects.getCourseId());
        pstmt.setString(3,subjects.getStem());
        pstmt.setString(4,subjects.getOptionA());
        pstmt.setString(5,subjects.getOptionB());
        pstmt.setString(6,subjects.getOptionC());
        pstmt.setString(7,subjects.getOptionD());
        pstmt.setString(8,subjects.getOptionE());
        pstmt.setString(9,subjects.getOptionF());
        pstmt.setString(10,subjects.getOptionG());
        pstmt.setString(11,subjects.getOptionH());
        pstmt.setString(12,subjects.getCorrect());
        pstmt.setInt(13,subjects.getHard());
        //使用预编译语句对象执行SQL语句，返回影响的行数
        rows=pstmt.executeUpdate();
    } catch (
    SQLException e) {
        e.printStackTrace();
        //抛出运行时异常
        throw new RuntimeException(e.getMessage(),e);
    } finally {
        //关闭/释放资源
        JdbcUtils.close(null,pstmt,conn);
    }
        //返回影响的行数
        return rows;
    }

    /**
     * 根据题库编号主键从数据库subjects表中删除指定题库数据
     * @param subjectId 题库编号主键
     * @return 删除影响的行数(1 : 删除成功 ; 0 : 删除失败)
     */
    @Override
    public int delete(int subjectId) {
        //声明数据库连接对象
        Connection conn=null;
        //声明预编译语句对象
        PreparedStatement pstmt=null;
        //定义影响行数的变量
        int rows=0;
        try {
            //实例化数据库连接对象
            conn= JdbcUtils.getConnection();
            //编写SQL语句字符串
            String sql="delete from subjects where subjectId=?";
            //实例化预编译语句对象
            pstmt=conn.prepareStatement(sql);
            //设置SQL语句中占位符赋值
            pstmt.setInt(1,subjectId);
            //使用预编译语句对象执行SQL语句，返回影响的行数
            rows=pstmt.executeUpdate();
        } catch (SQLException e) {
            //抛出运行时异常
            throw new RuntimeException(e.getMessage(),e);
        } finally {
            //关闭/释放资源
            JdbcUtils.close(null,pstmt,conn);
        }
        //返回影响的行数
        return rows;
    }

    /**
     * 将员工对象数据修改到数据库subjects表中
     * @param subjects 题库对象
     * @return 修改影响的行数(1 : 修改成功 ; 0 : 修改失败)
     */
    @Override
    public int update(Subjects subjects) {
        //声明数据库连接对象
        Connection conn=null;
        //声明预编译语句对象
        PreparedStatement pstmt=null;
        //定义影响行数的变量
        int rows=0;
        try {
            //实例化数据库连接对象
            conn= JdbcUtils.getConnection();
            //编写SQL语句字符串
            String sql="update subjects set subjectType=?,courseId=?,stem=?,optionA=?,optionB=?,optionC=?,optionD=?," +
                    "optionE=?,optionF=?,optionG=?,optionH=?,correct=?,hard=? where subjectId=?";
            //实例化预编译语句对象
            pstmt=conn.prepareStatement(sql);
            //设置SQL语句中占位符赋值
            pstmt.setString(1,subjects.getSubjectType());
            pstmt.setInt(2,subjects.getCourseId());
            pstmt.setString(3,subjects.getStem());
            pstmt.setString(4,subjects.getOptionA());
            pstmt.setString(5,subjects.getOptionB());
            pstmt.setString(6,subjects.getOptionC());
            pstmt.setString(7,subjects.getOptionD());
            pstmt.setString(8,subjects.getOptionE());
            pstmt.setString(9,subjects.getOptionF());
            pstmt.setString(10,subjects.getOptionG());
            pstmt.setString(11,subjects.getOptionH());
            pstmt.setString(12,subjects.getCorrect());
            pstmt.setInt(13,subjects.getHard());
            pstmt.setInt(14,subjects.getSubjectId());
            //使用预编译语句对象执行SQL语句，返回影响的行数
            rows=pstmt.executeUpdate();
        } catch (SQLException e) {
            //抛出运行时异常
            throw new RuntimeException(e.getMessage(),e);
        } finally {
            //关闭/释放资源
            JdbcUtils.close(null,pstmt,conn);
        }
        //返回影响的行数
        return rows;
    }

    /**
     * 根据题库编号主键从数据库subject表中查找单个数据
     * @param subjectId 题库编号主键
     * @return 题库对象(如果返回null值 ， 表示查找失败)
     */
    @Override
    public Subjects findById(String subjectId) {
        //声明数据库连接对象
        Connection conn=null;
        //声明预编译语句对象
        PreparedStatement pstmt=null;
        //声明结果集对象
        ResultSet rs=null;
        //定义返回的题库对象
        Subjects subjects=null;
        try {
            //实例化数据库连接对象
            conn= JdbcUtils.getConnection();
            //编写SQL语句字符串
            String sql="select subjectId,subjectType,courseId,stem,optionA,optionB,optionC,optionD,optionE,optionF," +
                    "optionG,optionH,correct,hard from subjects where subjectId=?";
            //实例化预编译语句对象
            pstmt=conn.prepareStatement(sql);
            //设置SQL语句中占位符赋值
            pstmt.setString(1,subjectId);
            //使用预编译语句对象执行SQL语句，返回结果集
            rs=pstmt.executeQuery();
            //判断结果集，并把结果集的数据封装到题库对象中
            if(rs.next()){
                //实例化题库对象
                subjects=new Subjects();
                //把结果集数据赋值给题库对象各属性
                subjects.setSubjectId(rs.getInt("subjectId"));
                subjects.setSubjectType(rs.getString("subjectType"));
                subjects.setCourseId(rs.getInt("courseId"));
                subjects.setStem(rs.getString("stem"));
                subjects.setOptionA(rs.getString("optionA"));
                subjects.setOptionB(rs.getString("optionB"));
                subjects.setOptionC(rs.getString("optionC"));
                subjects.setOptionD(rs.getString("optionD"));
                subjects.setOptionE(rs.getString("optionE"));
                subjects.setOptionF(rs.getString("optionF"));
                subjects.setOptionG(rs.getString("optionG"));
                subjects.setOptionH(rs.getString("optionH"));
                subjects.setCorrect(rs.getString("correct"));
                subjects.setHard(rs.getInt("hard"));
            }
        } catch (SQLException e) {
            //抛出运行时异常
            throw new RuntimeException(e.getMessage(),e);
        } finally {
            //关闭/释放资源
            JdbcUtils.close(rs,pstmt,conn);
        }
        //返回题库对象
        return subjects;
    }

    /**
     * 查询数据库subjects表中所有题库
     * @return 题库对象集合
     */
    @Override
    public List<Subjects> findAll() {
        //声明数据库连接对象
        Connection conn=null;
        //声明预编译语句对象
        PreparedStatement pstmt=null;
        //声明结果集对象
        ResultSet rs=null;
        //定义返回的题库对象集合
        List<Subjects> subjectsList=new ArrayList<Subjects>();
        try {
            //实例化数据库连接对象
            conn= JdbcUtils.getConnection();
            //编写SQL语句字符串
            String sql="select subjectId,subjectType,courseId,stem,optionA,optionB,optionC,optionD,optionE,optionF,optionG,optionH,correct,hard from subjects";
            //实例化预编译语句对象
            pstmt=conn.prepareStatement(sql);
            //使用预编译语句对象执行SQL语句，返回结果集
            rs=pstmt.executeQuery();
            //循环判断结果集，并把结果集的数据封装到对象中
            while(rs.next()){
                //定义题库对象
                Subjects subjects=new Subjects();
                //把结果集数据赋值给题库对象各属性
                subjects.setSubjectId(rs.getInt("subjectId"));
                subjects.setSubjectType(rs.getString("subjectType"));
                subjects.setCourseId(rs.getInt("courseId"));
                subjects.setStem(rs.getString("stem"));
                subjects.setOptionA(rs.getString("optionA"));
                subjects.setOptionB(rs.getString("optionB"));
                subjects.setOptionC(rs.getString("optionC"));
                subjects.setOptionD(rs.getString("optionD"));
                subjects.setOptionE(rs.getString("optionE"));
                subjects.setOptionF(rs.getString("optionF"));
                subjects.setOptionG(rs.getString("optionG"));
                subjects.setOptionH(rs.getString("optionH"));
                subjects.setCorrect(rs.getString("correct"));
                subjects.setHard(rs.getInt("hard"));
                //把题库对象添加题库集合中
                subjectsList.add(subjects);
            }
        } catch (SQLException e) {
            //抛出运行时异常
            throw new RuntimeException(e.getMessage(),e);
        } finally {
            //关闭/释放资源
            JdbcUtils.close(rs,pstmt,conn);
        }
        //返回题库对象集合
        return subjectsList;
    }

    /**
     * 从数据库subjects表中查询指定页的题库对象集合
     *
     * @param rows  每页行数
     * @param pages 页数
     * @return 当前页的题库对象集合
     */
    @Override
    public List<Subjects> findByPage(int rows, int pages) {
        return null;
    }


//    /**
//     * 从数据库subjects表中查询指定页的题库对象集合
//     *
//     * @param rows  每页行数
//     * @param pages 页数
//     * @return 当前页的题库对象集合
//     */
//    @Override
//    public List<Subjects> findByPage(int rows, int pages) {
//        //声明数据库连接对象
//        Connection conn=null;
//        //声明预编译语句对象
//        PreparedStatement pstmt=null;
//        //声明结果集对象
//        ResultSet rs=null;
//        //定义返回的对象集合
//        List<SubjectsTo> subjectsList=new ArrayList<SubjectsTo>();
//        try {
//            //实例化数据库连接对象
//            conn= JdbcUtils.getConnection();
//            //编写SQL语句字符串
//            String sql="SELECT sub.subjectId,sub.subjectType,c.courseName,sub.stem,sub.optionA,sub.optionB,sub.optionC,sub.optionD" +
//                    ",sub.optionE,sub.optionF,sub.optionG,sub.optionH,sub.correct,sub.hard FROM" +
//                    " subjects sub JOIN course c ON sub.courseId=c.courseId limit ?,?";
//            //实例化预编译语句对象
//            pstmt=conn.prepareStatement(sql);
//            //给分页语句占位符赋值
//            pstmt.setInt(1,(pages-1)*rows);
//            pstmt.setInt(2,rows);
//            //使用预编译语句对象执行SQL语句，返回结果集
//            rs=pstmt.executeQuery();
//            //循环判断结果集，并把结果集的数据封装到对象中
//            while(rs.next()){
//                //定义对象
//                Subjects subjects=new SubjectsTo();
//                //把结果集数据赋值给员工对象各属性
//                subjectsTo.setSubjectId(rs.getInt("subjectId"));
//                subjectsTo.setSubjectType(rs.getString("subjectType"));
//                subjectsTo.setCourseName(rs.getString("courseName"));
//                subjectsTo.setStem(rs.getString("stem"));
//                subjectsTo.setOptionA(rs.getString("optionA"));
//                subjectsTo.setOptionB(rs.getString("optionB"));
//                subjectsTo.setOptionC(rs.getString("optionC"));
//                subjectsTo.setOptionD(rs.getString("optionD"));
//                subjectsTo.setOptionE(rs.getString("optionE"));
//                subjectsTo.setOptionF(rs.getString("optionF"));
//                subjectsTo.setOptionG(rs.getString("optionG"));
//                subjectsTo.setOptionH(rs.getString("optionH"));
//                subjectsTo.setCorrect(rs.getString("correct"));
//                subjectsTo.setHard(rs.getInt("hard"));
//                //把题库对象添加题库集合中
//                subjectsList.add(subjectsTo);
//            }
//        } catch (SQLException e) {
//            //抛出运行时异常
//            throw new RuntimeException(e.getMessage(),e);
//        } finally {
//            //关闭/释放资源
//            JdbcUtils.close(rs,pstmt,conn);
//        }
//        //返回对象集合
//        return subjectsList;
//    }

    /**
     * 统计数据库subjects表中记录总数
     *
     * @return 移库表中记录总数
     */
    @Override
    public int count() {
        //声明数据库连接对象
        Connection conn=null;
        //声明预编译语句对象
        PreparedStatement pstmt=null;
        //声明结果集对象
        ResultSet rs=null;
        //定义返回表中总数据行数
        int rowCount=0;
        try {
            //实例化数据库连接对象
            conn= JdbcUtils.getConnection();
            //编写SQL语句字符串
            String sql="select count(*) as subjectscount from subjects";
            //实例化预编译语句对象
            pstmt=conn.prepareStatement(sql);
            //使用预编译语句对象执行SQL语句，返回结果集合
            rs=pstmt.executeQuery();
            //判断结果集，并获得总行数
            if(rs.next()){
                //如果统计行数时没有指定别名，则用数字0作为参数
                rowCount=rs.getInt("subjectscount");
            }
        } catch (SQLException e) {
            //抛出运行时异常
            throw new RuntimeException(e.getMessage(),e);
        } finally {
            //关闭/释放资源
            JdbcUtils.close(null,pstmt,conn);
        }
        //返回表中数据总行数
        return rowCount;
    }

    /**
     * 条件分页查询
     *
     * @param rows      每页行数
     * @param pages     页数
     * @param condition 条件
     * @return 题库对象集合
     */
    @Override
    public List<SubjectsTo> findByPage(int rows, int pages, String condition) {
        //声明数据库连接对象
        Connection conn=null;
        //声明预编译语句对象
        PreparedStatement pstmt=null;
        //声明结果集对象
        ResultSet rs=null;
        //定义返回的题库对象集合
        List<SubjectsTo> subjectsToList=new ArrayList<SubjectsTo>();
        try {
            //实例化数据库连接对象
            conn= JdbcUtils.getConnection();
            //编写SQL语句字符串
            String sql="SELECT sub.subjectId,sub.subjectType,c.courseName,sub.stem,sub.optionA,sub.optionB,sub.optionC,sub.optionD,sub.optionE,sub.optionF,sub.optionG,sub.optionH,sub.correct,sub.hard FROM subjects sub JOIN course c ON sub.courseId=c.courseId where 1=1 "+condition+" limit ?,?";
            //实例化预编译语句对象
            pstmt=conn.prepareStatement(sql);
            //给分页语句占位符赋值
            //分页语句：select * from 表名 limits (pages-1)*rows,rows
            pstmt.setInt(1,(pages-1)*rows);
            pstmt.setInt(2,rows);
            //使用预编译语句对象执行SQL语句，返回结果集
            rs=pstmt.executeQuery();
            //循环判断结果集，并把结果集的数据封装到员工对象中
            while(rs.next()){
                //定义员工对象
                SubjectsTo subjectsTo=new SubjectsTo();
                //把结果集数据赋值给对象各属性
                subjectsTo.setSubjectId(rs.getInt("subjectId"));
                subjectsTo.setSubjectType(rs.getString("subjectType"));
                subjectsTo.setCourseName(rs.getString("courseName"));
                subjectsTo.setStem(rs.getString("stem"));
                subjectsTo.setOptionA(rs.getString("optionA"));
                subjectsTo.setOptionB(rs.getString("optionB"));
                subjectsTo.setOptionC(rs.getString("optionC"));
                subjectsTo.setOptionD(rs.getString("optionD"));
                subjectsTo.setOptionE(rs.getString("optionE"));
                subjectsTo.setOptionF(rs.getString("optionF"));
                subjectsTo.setOptionG(rs.getString("optionG"));
                subjectsTo.setOptionH(rs.getString("optionH"));
                subjectsTo.setCorrect(rs.getString("correct"));
                subjectsTo.setHard(rs.getInt("hard"));
                //把题库对象添加题库集合中
                subjectsToList.add(subjectsTo);
            }
        } catch (SQLException e) {
            //抛出运行时异常
            throw new RuntimeException(e.getMessage(),e);
        } finally {
            //关闭/释放资源
            JdbcUtils.close(rs,pstmt,conn);
        }
        //返回员工对象集合
        return subjectsToList;
    }

    /**
     * 条件统计
     *
     * @param condition 条件
     * @return 题目数
     */
    @Override
    public int count(String condition) {
        //声明数据库连接对象
        Connection conn=null;
        //声明预编译语句对象
        PreparedStatement pstmt=null;
        //声明结果集对象
        ResultSet rs=null;
        //定义返回表中总数据行数
        int rowCount=0;
        try {
            //实例化数据库连接对象
            conn= JdbcUtils.getConnection();
            //编写SQL语句字符串
            String sql="select count(*) as subjectscount from subjects where 1=1 "+condition;
            //实例化预编译语句对象
            pstmt=conn.prepareStatement(sql);
            //使用预编译语句对象执行SQL语句，返回结果集合
            rs=pstmt.executeQuery();
            //判断结果集，并获得总行数
            if(rs.next()){
                //如果统计行数时没有指定别名，则用数字0作为参数
                rowCount=rs.getInt("subjectscount");
            }
        } catch (SQLException e) {
            //抛出运行时异常
            throw new RuntimeException(e.getMessage(),e);
        } finally {
            //关闭/释放资源
            JdbcUtils.close(null,pstmt,conn);
        }
        //返回表中数据总行数
        return rowCount;
    }

    @Override
    public String getCondition(Subjects subjects) {
        //定义查询条件字符串，初值为带空格的字符串(注意后续条件中的空格)
        String condition=" ";
        //如果对象为空，则直接返回空字符串
        if(subjects==null){
            return condition;
        }
        //试题编号
        if(subjects.getSubjectId()!=0){
            condition += " and subjectId="+subjects.getSubjectId();
        }
        //试题类型
        if(subjects.getSubjectType()!=null && subjects.getSubjectType().trim().length()!=0){
            condition += " and subjectType like '%"+subjects.getSubjectType()+"%' ";
        }
        //科目，来自course表--外键
        if(subjects.getCourseId()!=0){
            condition += " and courseId="+subjects.getCourseId();
        }
        //题干
        if(subjects.getStem()!=null && subjects.getStem().trim().length()!=0){
            condition += " and stem like '%"+subjects.getStem()+"%' ";
        }
        //选项A
        if(subjects.getOptionA()!=null && subjects.getOptionA().trim().length()!=0){
            condition += " and optionA like '%"+subjects.getOptionA()+"%' ";
        }
        //选项B
            if(subjects.getOptionB()!=null && subjects.getOptionB().trim().length()!=0){
            condition += " and optionB like '%"+subjects.getOptionB()+"%' ";
        }
        //选项C
        if(subjects.getOptionC()!=null && subjects.getOptionC().trim().length()!=0){
            condition += " and optionC like '%"+subjects.getOptionC()+"%' ";
        }
        //选项D
        if(subjects.getOptionD()!=null && subjects.getOptionD().trim().length()!=0){
            condition += " and optionD like '%"+subjects.getOptionD()+"%' ";
        }
        //选项E
        if(subjects.getOptionE()!=null && subjects.getOptionE().trim().length()!=0){
            condition += " and optionE like '%"+subjects.getOptionE()+"%' ";
        }
        //选项F
        if(subjects.getOptionF()!=null && subjects.getOptionF().trim().length()!=0){
            condition += " and optionF like '%"+subjects.getOptionF()+"%' ";
        }
        //选项G
        if(subjects.getOptionG()!=null && subjects.getOptionG().trim().length()!=0){
            condition += " and optio nG like '%"+subjects.getOptionG()+"%' ";
        }
        //选项H
        if(subjects.getOptionH()!=null && subjects.getOptionH().trim().length()!=0){
            condition += " and optionH like '%"+subjects.getOptionH()+"%' ";
        }
        //正确答案
        if(subjects.getCorrect()!=null && subjects.getCorrect().trim().length()!=0){
            condition += " and correct like '%"+subjects.getCorrect()+"%' ";
        }
        //难度系数
        if(subjects.getHard()!=0){
            condition += " and hard="+subjects.getHard();
        }
        //返回条件字符串
        return condition;
    }

    /**
     * 查询所有班级名称
     *
     * @return
     */
    @Override
    public List<CourseName> findCourseName() {
        //声明数据库连接对象
        Connection conn=null;
        //声明预编译语句对象
        PreparedStatement pstmt=null;
        //声明结果集
        ResultSet rs=null;
        //对象结果集
        List<CourseName> courseNameList=new ArrayList<>();


        try {
            //实例化数据库
            conn= JdbcUtils.getConnection();
            //编写SQL语句
            String sql="select  courseName FROM course";
            //实例化预编译语句
            pstmt=conn.prepareStatement(sql);
            //返回结果集
            rs=pstmt.executeQuery();
            while (rs.next()){
                //定义课程名对象
                CourseName courseName=new CourseName();
                //给占位符赋值
                courseName.setCourseName (rs.getString("courseName"));
                //返回课程对象集合
                courseNameList.add(courseName);
            }

        } catch (SQLException e) {
            //抛出运行时异常
            throw new RuntimeException(e.getMessage(),e);
        }finally {
            //关闭/释放资源
            JdbcUtils.close(rs,pstmt,conn);
        }
        //返回集合对象
        return courseNameList;
    }

    /**
     * 通过课程名称查询课程编号
     *
     * @param courseName
     * @return
     */
    @Override
    public int findCourseId(String courseName) {
        //定义课程编号
        int courseId;
        //声明数据库连接对象
        Connection conn=null;
        //声明预编译语句
        PreparedStatement pstmt=null;
        //声明结果集
        ResultSet rs=null;

        try {
            //实例化数据库
            conn= JdbcUtils.getConnection();
            //编写sql语句
            String sql="SELECT  courseId FROM course where courseName=? ";
            //实例化预编译语句
            pstmt=conn.prepareStatement(sql);
            //给占位符赋值
            pstmt.setString(1,courseName);
            //返回结果集
            rs=pstmt.executeQuery();
            if (rs.next()){
                //如果统计行数时没有指定别名，则用数字0作为参数
                courseId=rs.getInt("courseId");
                //返回课程编号
                return courseId;
            }

        } catch (SQLException e) {
            throw new RuntimeException(e.getMessage(),e);
        }finally {
            //关闭/释放资源
            JdbcUtils.close(rs,pstmt,conn);
        }
        return 0;
    }
}
